﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Data;
using DbSharp.Utils.Db;
using MySql.Data.Types;
using System.Globalization;
using DbSharp.Framework.DbObject;
using DbSharp.Framework.Common;
using DbSharp.Framework.Driver.FieldPersistence;

namespace DbSharp.Framework.Driver
{
    //Since there's no support in the protocol for an RPC call or binding directionality of parameters using server-side prepared statements for stored procedures
    //you see a hint that MySQL 6 might introduce proper server-side support for OUT parameters.
    //Bug: MySql.Data.dll 5.2.5 still has a bug, It can not retrieve the output type parameter value as expected. 
    //Solve hint: http://www.paragon-cs.com/mag/issue3.pdf


    /// <summary>
    /// 
    /// MySQL 5.0 New Features: Stored Procedures
    ///http://www.java2s.com/Tutorial/MySQL/CatalogMySQL.htm
    ///http://www.justintubbs.com/code-samples/mysql-stored-procedures.php
    ///http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html
    ///http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf
    ///MySQL Ado.net provider http://downloads.mysql.com/docs/connector-net-en.a4.pdf
    ///http://www.mysql.com/doc/en/Stored_Procedures.html
    /// </summary>
    public class MySqlTestPlugin : ITestDataSource
    {
        private const int RECORDSET_ARGUMENT_FLAG = -1;

        #region Private method

        /// <summary>
        /// remove all semicolon symbols tailed the sql statement
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private string RemoveTailedSemiColons(string sql)
        {
            return SqlTextHelper.RightTrimCharacter(sql, ';');
        }


        private int GetArgumentType(string argumentTypeText)
        {
            int parameterType;
            string upperTypeStr = argumentTypeText.Trim().ToUpper();
            switch (upperTypeStr)
            {

                case "MYSQL_DECIMAL":
                    parameterType = (int)MySqlDbType.Decimal;
                    break;

                case "MYSQL_BYTE":
                    parameterType = (int)MySqlDbType.Byte;
                    break;

                case "MYSQL_INT16":
                    parameterType = (int)MySqlDbType.Int16;
                    break;

                case "MYSQL_INT32":
                    parameterType = (int)MySqlDbType.Int32;
                    break;

                case "MYSQL_FLOAT":
                    parameterType = (int)MySqlDbType.Float;
                    break;

                case "MYSQL_DOUBLE":
                    parameterType = (int)MySqlDbType.Double;
                    break;

                case "MYSQL_TIMESTAMP":
                    parameterType = (int)MySqlDbType.Timestamp;
                    break;

                case "MYSQL_INT64":
                    parameterType = (int)MySqlDbType.Int64;
                    break;

                case "MYSQL_INT24":
                    parameterType = (int)MySqlDbType.Int24;
                    break;

                case "MYSQL_DATE":
                    parameterType = (int)MySqlDbType.Date;
                    break;

                case "MYSQL_TIME":
                    parameterType = (int)MySqlDbType.Time;
                    break;

                //MySQL_Datetime,  obsolete, use MySqlDbType.DateTime
                case "MYSQL_DATETIME":
                    parameterType = (int)MySqlDbType.DateTime;
                    break;

                case "MYSQL_YEAR":
                    parameterType = (int)MySqlDbType.Year;
                    break;

                //MySQL_Newdate, obsolete

                case "MYSQL_VARSTRING":
                    parameterType = (int)MySqlDbType.VarString;
                    break;

                case "MYSQL_BIT":
                    parameterType = (int)MySqlDbType.Bit;
                    break;

                case "MYSQL_NEWDECIMAL":
                    parameterType = (int)MySqlDbType.NewDecimal;
                    break;

                case "MYSQL_ENUM":
                    parameterType = (int)MySqlDbType.Enum;
                    break;

                case "MySQL_Set":
                    parameterType = (int)MySqlDbType.Set;
                    break;
                //MySQL_TinyBlob
                //MySQL_MediumBlob
                //MySQL_LongBlob
                //MySQL_Blob

                case "MYSQL_VARCHAR":
                    parameterType = (int)MySqlDbType.VarChar;
                    break;

                case "MYSQL_STRING":
                    parameterType = (int)MySqlDbType.String;
                    break;

                //MySQL_Geometry 

                case "MYSQL_UBYTE":
                    parameterType = (int)MySqlDbType.UByte;
                    break;

                case "MYSQL_UINT16":
                    parameterType = (int)MySqlDbType.UInt16;
                    break;

                case "MYSQL_UINT32":
                    parameterType = (int)MySqlDbType.UInt32;
                    break;

                case "MYSQL_UINT64":
                    parameterType = (int)MySqlDbType.UInt64;
                    break;

                case "MYSQL_UINT24":
                    parameterType = (int)MySqlDbType.UInt24;
                    break;
                //MySQL_Binary
                //MySQL_VarBinary

                case "MYSQL_TINYTEXT":
                    parameterType = (int)MySqlDbType.TinyText;
                    break;
                //MySQL_MediumText
                //MySQL_LongText
                //MySQL_Text

                case "MYSQL_RECORDSET":
                    parameterType = RECORDSET_ARGUMENT_FLAG;
                    break;
                default:
                    parameterType = (int)MySqlDbType.VarChar;
                    break;
            }
            return parameterType;
        }

        #endregion


        #region ITestDataSource Members

        public DateTime GetDbServerTime(DbConnection conn)
        {
            string sql = "SELECT NOW(); ";
            DbCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            DateTime result = (DateTime)cmd.ExecuteScalar();
            return result;
        }

        public bool IsRecordsetTypeArgument(string argumentTypeText)
        {
            if (GetArgumentType(argumentTypeText) == RECORDSET_ARGUMENT_FLAG)
                return true;
            else
                return false;
        }

        public bool IsRecordsetTypeParameter(DbParameter param)
        {
            return false;
        }

        public DbConnection CreateConnection(string connString)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = connString;
            return conn; 
        }

        public string NormalizeArgumentName(string argumentName)
        {
            string arg;
            arg = SqlTextHelper.LeftTrimCharacter(argumentName, '@');
            arg = SqlTextHelper.BeginWithCharacter(argumentName, '?');
            return arg;
        }

        public DbCommand PrepareCommand(IStatementDeclaration statement)
        {
            DbCommand cmd = new MySqlCommand();
            cmd.CommandType = statement.CommandType;
 
            IList<StatementArgument> listArgumentObj = statement.ListArgument;
            foreach (var argumentObj in listArgumentObj)
            {
                if (argumentObj.Direction == ArgumentDirection.ClientSide)
                {
                    //Clientside parameter will not added into ParameterList for ever
                    continue;
                }
                else if (argumentObj.IsSqlStmtResultArg)
                {
                    //It means this argument is virtual argument, not the real argument
                    // So the argument will not be added into ParameterList 
                    continue;
                }

                if (argumentObj.Direction == ArgumentDirection.AnonymousOutput)
                {
                    //Sql server will use anonymous arguments to hold resultsets, not need add these arguments into parameterlist
                    continue;
                }

                var parameter = new MySqlParameter();
                parameter.Direction = ArgumentDirectionHelper.ConvertToParamDirection(argumentObj.Direction);
                parameter.MySqlDbType = (MySqlDbType)GetArgumentType(argumentObj.TypeStr);
                parameter.Size = argumentObj.Size;
                parameter.ParameterName =NormalizeArgumentName(argumentObj.Name);

                cmd.Parameters.Add(parameter);
            }

            return cmd;
        }


        public void UpdateCommandText(DbCommand cmd, string newCmdText)
        {
            cmd.CommandText = newCmdText.Trim();
            //make sure the sql statement does not end with semicolon
            if (cmd.CommandType == CommandType.Text)
            {
                cmd.CommandText = RemoveTailedSemiColons(cmd.CommandText);
            }
        }


        public void AssignParameterValue(DbParameter param, string value, string dateTimeFormat) 
        {
            MySqlParameter parameter = (MySqlParameter)param;

            if ((parameter.MySqlDbType == MySqlDbType.Date)
                || (parameter.MySqlDbType == MySqlDbType.DateTime)
                || (parameter.MySqlDbType == MySqlDbType.Time)
                || (parameter.MySqlDbType == MySqlDbType.Timestamp)
                )
            {
                if (string.IsNullOrEmpty(dateTimeFormat))
                {
                    MySqlDateTime myDateTime = new MySqlDateTime(value);
                    parameter.Value = myDateTime.GetDateTime();
                }
                else
                { 
                    parameter.Value = DateTimeParser.ParseExactUtc(value, dateTimeFormat);
                }
            }
            else
                parameter.Value = value;
        }


        public bool TransactionSupported
        {
            get { return true ; }
        }


        /// <summary>
        /// MySql is SQL Server style, not Oracle sytle
        /// </summary>
        public bool HoldResultsetMustWithNamedArgInSP
        {
            get { return false; }
        }



     

        private IFieldPersistence m_FieldPersistenceService = new MySqlFieldPersistence();
        public IFieldPersistence FieldPersistenceService
        {
            get { return m_FieldPersistenceService; }
        }
        #endregion
    }
}
